/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package ru.miit.elearning.hibernate.dao;

import java.util.Calendar;
import java.util.Iterator;
import java.util.List;
import java.util.Set;
import org.hibernate.Criteria;
import org.hibernate.Session;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Restrictions;
import ru.miit.elearning.hibernate.dto.CurriculumLevel;
import ru.miit.elearning.hibernate.dto.CurriculumLevelType;
import ru.miit.elearning.hibernate.dto.UserData;
import ru.miit.elearning.hibernate.dto.UserDataId;
import ru.miit.elearning.hibernate.dto.UserIdentity;
import ru.miit.elearning.hibernate.dto.WorkItem;
import ru.miit.elearning.hibernate.dto.WorkItemId;

/**
 * Data access object to perform database work with courses.
 * @author Cyclone
 */
public class CourseDAO extends GenericDAO {

    /* ================ COMMON SECTION ====================================== */
    public CourseDAO(Session session) {
        super(session);
    }

    /**
     * Adds new direction curriculum level
     * @param title the title of new direction
     * @return created direction curriculum level
     */
    public CurriculumLevel addDirection(String title) {
        CurriculumLevel direction = new CurriculumLevel();
        direction.setCurriculumLevelType(new CurriculumLevelType(CURRICULUM_LEVEL_TYPE_DIRECTION));
        direction.setTitle(title);
        int id = (Integer) session.save(direction);
        //session.getTransaction().commit();

        //session = HibernateUtil.getSessionFactory().getCurrentSession();
        //session.beginTransaction();
        return (CurriculumLevel) session.get(CurriculumLevel.class, id);
    }

    /**
     * Adds new course curriculum level
     * @param direction id of direction that should contain a course
     * @param courseTitle the title of new course
     * @param courseNumber the numeral of new course. If requested numeral is already taken, the numeral would take first free value in the direction.
     * @param description the description of new course
     * @return created course curriculum level
     */
    public CurriculumLevel addCourse(int direction, String courseTitle, Integer courseNumber, String description) {
        if (courseNumber != null) {
            courseNumber = Math.max(1, courseNumber);
        }
        //check is courseNumber free or taken
        if (courseNumber == null || session.createQuery("from CurriculumLevel "
                + "where curriculumLevelType.id = :courseType "
                + "and curriculumLevel.id = :direction "
                + "and numeral = :numeral").setParameter("courseType", CURRICULUM_LEVEL_TYPE_COURSE).setParameter("direction", direction).setParameter("numeral", courseNumber).uniqueResult() != null) {
            Integer currentCourseNumber = (Integer) session.createQuery("select max(numeral) from CurriculumLevel "
                    + "where curriculumLevelType.id = :courseType "
                    + "and curriculumLevel.id = :direction").setParameter("courseType", CURRICULUM_LEVEL_TYPE_COURSE).setParameter("direction", direction).uniqueResult();
            courseNumber = 1 + (currentCourseNumber != null ? currentCourseNumber : 0);
        }



        CurriculumLevel course = new CurriculumLevel();
        course.setCurriculumLevelType(new CurriculumLevelType(CURRICULUM_LEVEL_TYPE_COURSE));
        course.setCurriculumLevel((CurriculumLevel) session.get(CurriculumLevel.class, direction));
        course.setTitle(courseTitle);
        course.setNumeral(courseNumber);
        course.setDescription(description);
        int id = (Integer) session.save(course);
        /*
        session.getTransaction().commit();

        session = HibernateUtil.getSessionFactory().getCurrentSession();
        session.beginTransaction();
         */
        return (CurriculumLevel) session.get(CurriculumLevel.class, id);
    }

    /**
     * Finds all curriculum levels with type = GenericDAO.CURRICULUM_LEVEL_TYPE_DIRECTION
     * @return list of curriculum levels
     */
    public List<CurriculumLevel> findAllDirections() {
        return session.createCriteria(CurriculumLevel.class).add(Restrictions.eq("curriculumLevelType.id", CURRICULUM_LEVEL_TYPE_DIRECTION)).addOrder(Order.asc("title")).list();
    }

    /**
     * Finds all curriculum levels with type = GenericDAO.CURRICULUM_LEVEL_TYPE_COURSE
     * @param sort sort parameter. Leave it null to sort list by parent curriculum level title and course number. It may also be "id" or "title" or any sortable field of curriculum level.
     * @return list of curriculum levels
     */
    public List<CurriculumLevel> findAllCourses(String sort) {
        return createAllCoursesCriteria(sort).list();
    }

    public CurriculumLevel findCourse(int courseId) {
        return (CurriculumLevel) session.get(CurriculumLevel.class, courseId);
    }

    public String findCourseTitle(int courseId) {
        new CurriculumLevel().getId();
        return ((CurriculumLevel) session.createCriteria(CurriculumLevel.class).add(Restrictions.eq("id", courseId)).uniqueResult()).getTitle();
    }

    /**
     * Counts a number of students, signed on course
     * @param courseId
     * @return number of students
     */
    public long countStudentsByCourse(int courseId) {
        return (Long) session.createQuery("select distinct count(ud) from UserData ud "
                + "where ud.id.curriculumLevel = :courseId "
                + "and ud.id.profileType = :studentType").setParameter("courseId", courseId).setParameter("studentType", PROFILE_TYPE_STUDENT).uniqueResult();

    }

    /* ================ / COMMON SECTION ==================================== */
    /* ================ LAB SECTION ========================================= */
    /**
     * Finds a page of labs: curriculum levels with type = GenericDAO.CURRICULUM_LEVEL_TYPE_LAB
     * @param min
     * @param count
     * @param sort sort parameter. Leave it null to sort list by parent course number. It may also be "id" or "title" or any sortable field of curriculum level.
     * @return list of curriculum levels
     */
    public List<CurriculumLevel> findLabsByPage(int min, int count, String sort) {
        String query = "from CurriculumLevel where curriculumLevelType.id = :labType";

        if (sort == null || sort.trim().equals("") || sort.trim().equals("null")) {
            sort = "curriculumLevel.title, numeral";
        }

        query += " order by " + sort;
        min = Math.max(min, 0);
        return session.createQuery(query).setParameter("labType", CURRICULUM_LEVEL_TYPE_LAB).setFirstResult(min).setMaxResults(count).list();
    }

    /* ================ / LAB SECTION ======================================= */
    /* ================ TUTOR SECTION ======================================= */
    /**
     * Finds all courses, on which a tutor is signed.
     * @param tutorId
     * @param sort sort parameter. Leave it null to sort list by parent curriculum level title and course number. It may also be "id" or "title" or any sortable field of curriculum level.
     * @return list of course curriculum levels.
     */
    public List<CurriculumLevel> findCoursesByTutor(int tutorId, String sort) {

        List<Integer> courseList = session.createQuery("select ud.id.curriculumLevel from UserData ud "
                + "where ud.id.account = :tutorId "
                + "and ud.id.profileType = :tutorType").setParameter("tutorId", tutorId).setParameter("tutorType", PROFILE_TYPE_TUTOR).list();
        if (courseList == null || courseList.size() < 1) {
            return null;
        }

        Criteria criteria = createAllCoursesCriteria(sort);
        criteria.add(Restrictions.in("id", courseList));

        return criteria.list();
    }

    /**
     * Finds a page of labs of courses, that a tutor is signed on.
     * @param min page start
     * @param count page size
     * @param sort sort parameter. Leave it null to sort list by parent curriculum level title and course number. It may also be "id" or "title" or any sortable field of curriculum level.
     * @param tutorId
     * @return
     */
    public List<CurriculumLevel> findLabsByPageByTutor(int min, int count, String sort, int tutorId) {
        //new UserData().getId().
        String query = "from CurriculumLevel cl "
                + "where cl.curriculumLevel.id in "
                + "(select ud2.curriculumLevel.id "
                + "from UserData ud2 "
                + "where ud2.id.account = :tutorId "
                + "and ud2.id.profileType = :tutorType "
                + "and ud2.id.laboratory = :laboratory)";

        if (sort == null || sort.trim().equals("") || sort.trim().equals("null")) {
            sort = "cl.curriculumLevel.title, cl.numeral";
        }

        query += " order by " + sort;
        min = Math.max(min, 0);

        return session.createQuery(query).setParameter("tutorId", tutorId).setParameter("tutorType", PROFILE_TYPE_TUTOR).setParameter("laboratory", LABORATORY).setFirstResult(min).setMaxResults(count).list();
    }

    /**
     * Finds all students, that are signed on course.
     * @param courseId
     * @return list of user identities.
     */
    public List<UserIdentity> findStudentsByCourse(int courseId) {
        return findUsersByCourse(courseId, PROFILE_TYPE_STUDENT);
    }

    /**
     * Finds all students, that are not signed on course.
     * @param courseId
     * @return list of user identities.
     */
    public List<UserIdentity> findStudentsByNoCourse(int courseId) {
        return findUsersByNoCourse(courseId, PROFILE_TYPE_STUDENT);
    }

    /**
     * Finds all tutors, that are signed on course.
     * @param courseId
     * @return list of user identities.
     */
    public List<UserIdentity> findTutorsByCourse(int courseId) {
        return findUsersByCourse(courseId, PROFILE_TYPE_TUTOR);
    }

    /**
     * Finds all tutors, that are not signed on course.
     * @param courseId
     * @return list of user identities.
     */
    public List<UserIdentity> findTutorsByNoCourse(int courseId) {
        return findUsersByNoCourse(courseId, PROFILE_TYPE_TUTOR);
    }

    /**
     * Signs student on course by inserting necessary rows into UserData table for course and in WorkItem table for course and each lab.
     * @param userId
     * @param courseId
     * @return a course curriculum level, that a student is signed on.
     */
    public CurriculumLevel signStudentOnCourse(int userId, int courseId) {

        //UserData userData =
        addUserData(userId, courseId, PROFILE_TYPE_STUDENT);
        /*
        UserData courseUserData = new UserData();
        courseUserData.setId(new UserDataId(PROFILE_TYPE_STUDENT, userId, LABORATORY, courseId));
        courseUserData.setActive(false);
        courseUserData.setRecordDate(Calendar.getInstance().getTime());
        session.saveOrUpdate(courseUserData);
         */

        addWorkItem(userId, courseId);
        /*
        WorkItem courseWI = new WorkItem();
        courseWI.setId(new WorkItemId(userId, courseId));
        session.saveOrUpdate(courseWI);
         */
        List<CurriculumLevel> labList = session.createQuery("from CurriculumLevel cl "
                + "where cl.curriculumLevelType.id = :labType "
                + "and cl.curriculumLevel.id = :courseId").setParameter("labType", CURRICULUM_LEVEL_TYPE_LAB).setParameter("courseId", courseId).list();

        //userData.getCurriculumLevel().getCurriculumLevels();

        for (CurriculumLevel lab : labList) {
            //addWorkItem(userId, lab.getId());
            WorkItem labWI = new WorkItem();
            labWI.setId(new WorkItemId(userId, lab.getId()));
            session.saveOrUpdate(labWI);
        }

        return (CurriculumLevel) session.get(CurriculumLevel.class, courseId);
    }

    /**
     * Signs student off course by removing corresponding rows from UserData table for course and in WorkItem table for course and each lab.
     * @param userId
     * @param courseId
     * @return title of the course
     */
    public String signStudentOffCourse(int userId, int courseId) {
        //new UserData().getId().getProfileType();
        UserData userData =
                (UserData) session.createQuery("from UserData ud where ud.id.account = :userId "
                + "and ud.id.curriculumLevel = :courseId "
                + "AND ud.id.profileType = :studentType").setParameter("userId", userId).setParameter("courseId", courseId).setParameter("studentType", PROFILE_TYPE_STUDENT).uniqueResult();

        Set<CurriculumLevel> labSet = userData.getCurriculumLevel().getCurriculumLevels();
        for (CurriculumLevel lab : labSet) {
            delUserWorkItem(userId, lab.getId());
        }

        delUserWorkItem(userId, courseId);

        String title = userData.getCurriculumLevel().getTitle();
        session.delete(userData);

        return title;
    }

    /**
     * Signs tutor on to check course by inserting a row into UserData table.
     * @param userId
     * @param courseId
     * @return the course curriculum level
     */
    public CurriculumLevel signTutorOnCourse(int userId, int courseId) {
        addUserData(userId, courseId, PROFILE_TYPE_TUTOR);
        return (CurriculumLevel) session.get(CurriculumLevel.class, courseId);
    }

    /**
     * Signs tutor off not to check course by deleting a row from UserData table.
     * @param userId
     * @param courseId
     * @return title of the course
     */
    public String signTutorOffCourse(Integer userId, Integer courseId) {
        UserDataId id = new UserDataId(PROFILE_TYPE_TUTOR, userId, LABORATORY, courseId);
        UserData userData = (UserData) session.get(UserData.class, id);

        String title = userData.getCurriculumLevel().getTitle();

        session.delete(userData);

        return title;
    }

    /* ================ / TUTOR SECTION ===================================== */
    /* ================ EDIT SECTION ======================================== */
    /**
     * Edits curriculum level.
     * @param curriculumLevelId
     * @param parentId
     * @param number
     * @param title
     * @param description
     */
    public void updateCurriculumLevel(int curriculumLevelId, int parentId, Integer number, String title, String description) {
        CurriculumLevel curriculumLevel = (CurriculumLevel) session.get(CurriculumLevel.class, curriculumLevelId);

        // если редактируем практическую работу
        if (curriculumLevel.getCurriculumLevelType().getId() == CURRICULUM_LEVEL_TYPE_LAB) {
            // и если меняется принадлежность к курсу
            if (curriculumLevel.getCurriculumLevel().getId() != parentId) {
                //то надо удалить work item'ы по практической работе студентов, зачисленных на предыдущий курс,
                //и создать их для студентов нового курса
                Set<WorkItem> workItemSet = curriculumLevel.getWorkItems();
                for (Iterator<WorkItem> it = workItemSet.iterator(); it.hasNext();) {
                    WorkItem workItem = it.next();
                    session.delete(workItem);
                }

                List<UserIdentity> studentList = findStudentsByCourse(parentId);
                for (UserIdentity userIdentity : studentList) {
                    WorkItemId id = new WorkItemId(userIdentity.getAccount(), curriculumLevel.getId());
                    WorkItem wi = new WorkItem(id, curriculumLevel, userIdentity.getAccount_1());
                    wi.setActive(Boolean.FALSE);
                    session.save(wi);
                }

            }
        }

        curriculumLevel.setCurriculumLevel((CurriculumLevel) session.get(CurriculumLevel.class, parentId));
        curriculumLevel.setTitle(title);
        if (number == null) {
            CurriculumLevel parentLevel = curriculumLevel.getCurriculumLevel();
            Integer maxNumber = 0;
            for (Iterator<CurriculumLevel> it = parentLevel.getCurriculumLevels().iterator(); it.hasNext();) {
                CurriculumLevel cl = it.next();
                if (cl.getNumeral() != null && cl.getNumeral().compareTo(maxNumber) > 0) {
                    maxNumber = cl.getNumeral();
                }
            }
            number = maxNumber + 1;
        }
        curriculumLevel.setNumeral(number);
        curriculumLevel.setDescription(description);

        session.saveOrUpdate(curriculumLevel);
    }

    /**
     * Chenges a number of curriculum level.
     * @param curriculumId
     * @param direction is "up" to make number lesser or "down" to make it bigger.
     */
    public void moveCurriculum(int curriculumId, String direction) {
        Integer currentNumberObj = (Integer) session.createQuery("select numeral from CurriculumLevel "
                + "where id = :curriculumId").setParameter("curriculumId", curriculumId).uniqueResult();

        int currentNumber = -1;
        if (currentNumberObj == null) {
            return;
        } else {
            currentNumber = currentNumberObj;
        }

        int requestedNumber = currentNumber;
        if (direction.equals("up")) {
            requestedNumber = Math.max(requestedNumber - 1, 1);
        } else {
            if (direction.equals("down")) {
                requestedNumber++;
            }
        }

        if (currentNumber == requestedNumber) {
            return;
        }

        // to find out, if requested number is already occupied
        CurriculumLevel curriculumLevelToReplace = (CurriculumLevel) session.createQuery("from CurriculumLevel "
                + "where numeral = :requestedNumber "
                + "and curriculumLevel.id = "
                + "(select cl.curriculumLevel.id "
                + "from CurriculumLevel cl "
                + "where cl.id = :curriculumId)").setParameter("requestedNumber", requestedNumber).setParameter("curriculumId", curriculumId).uniqueResult();

        CurriculumLevel curriculumLevelToMove = (CurriculumLevel) session.get(CurriculumLevel.class, curriculumId);

        if (curriculumLevelToReplace != null) {
            curriculumLevelToReplace.setNumeral(-1);
            session.update(curriculumLevelToReplace);

            curriculumLevelToMove.setNumeral(requestedNumber);
            session.update(curriculumLevelToMove);

            // couldn't do it without those terrible commits :(
            session.getTransaction().commit();
            session = HibernateUtil.getSessionFactory().getCurrentSession();
            session.beginTransaction();


            curriculumLevelToReplace.setNumeral(currentNumber);
            session.update(curriculumLevelToReplace);


            session.getTransaction().commit();
            session = HibernateUtil.getSessionFactory().getCurrentSession();
            session.beginTransaction();
        } else {
            curriculumLevelToMove.setNumeral(requestedNumber);
            session.update(curriculumLevelToMove);
        }
    }

    /**
     * Determines, if a user may edit a course: if he is a tutor of this course.
     * @param userId
     * @param courseId
     * @return true, if user may edit a course; otherwise - false
     */
    public boolean mayEdit(int userId, int courseId) {
        return (session.get(UserData.class, new UserDataId(PROFILE_TYPE_TUTOR, userId, LABORATORY, courseId)) != null);
    }

    /**
     * Signs all users off course and its labs, deletes all course's labs and course.
     * @param courseId
     * @return title of deleted course
     */
    public String deleteCourse(int courseId) {
        CurriculumLevel course = (CurriculumLevel) session.get(CurriculumLevel.class, courseId);
        String title = course.getTitle();

        Set<WorkItem> workItemSet = course.getWorkItems();
        for (Iterator<WorkItem> it = workItemSet.iterator(); it.hasNext();) {
            WorkItem workItem = it.next();
            session.delete(workItem);
        }

        Set<UserData> userDataSet = course.getUserDatas();
        for (Iterator<UserData> it = userDataSet.iterator(); it.hasNext();) {
            UserData userData = it.next();
            session.delete(userData);
        }

        Set<CurriculumLevel> labSet = course.getCurriculumLevels();
        for (Iterator<CurriculumLevel> it = labSet.iterator(); it.hasNext();) {
            CurriculumLevel lab = it.next();

            session.delete(lab);
        }

        session.delete(course);
        return title;
    }

    /* ================ / EDIT SECTION ====================================== */
    /* ================ TOOLS SECTION ======================================= */
    protected Criteria createAllCoursesCriteria(String sort) {
        Criteria criteria = session.createCriteria(CurriculumLevel.class).add(Restrictions.eq("curriculumLevelType.id", CURRICULUM_LEVEL_TYPE_COURSE));
        if (sort == null || sort.equals("curriculumLevel")) {
            criteria.addOrder(Order.asc("curriculumLevel"));
            criteria.addOrder(Order.asc("numeral"));
        } else {
            criteria.addOrder(Order.asc(sort));
        }

        return criteria;
    }

    protected void addWorkItem(int userId, int curriculumLevelId) {
        WorkItem wi = new WorkItem();
        wi.setId(new WorkItemId(userId, curriculumLevelId));
        wi.setActive(false);

        session.saveOrUpdate(wi);
    }

    protected UserData addUserData(int userId, int courseId, short profileType) {
        UserData userData = new UserData();
        UserDataId id = new UserDataId(profileType, userId, 1, courseId);

        userData.setId(id);
        userData.setRecordDate(Calendar.getInstance().getTime());

        id = (UserDataId) session.save(userData);
        //.saveOrUpdate(userData);
        return (UserData) session.get(UserData.class, id);
    }

    protected void delUserWorkItem(Integer userId, Integer courseId) {
        WorkItem wi = new WorkItem();
        wi.setId(new WorkItemId(userId, courseId));

        session.delete(wi);
    }

    /**
     * Finds all users of profile type, that are signed on course.
     * @param courseId
     * @param profileType should be one of the following:
     * <ul><li>GenericDAO.PROFILE_TYPE_STUDENT</li>
     * <li>GenericDAO.PROFILE_TYPE_TUTOR</li></ul>
     * @return list of user identities.
     */
    protected List<UserIdentity> findUsersByCourse(int courseId, short profileType) {
        return session.createQuery("from UserIdentity as ui "
                + "where ui.account in "
                + "(select distinct ud.profile.account.id "
                + "from UserData ud "
                + "where ud.profile.id.id = :profileType) "
                + "and  "
                + "ui.account in "
                + "(select ud2.profile.account.id "
                + "from UserData ud2 "
                + "where ud2.profile.id.id = :profileType "
                + "and ud2.curriculumLevel.id = :courseId) "
                + "order by ui.name").setParameter("profileType", profileType).setParameter("courseId", courseId).list();
    }

    /**
     * Finds all users of profile type, that are not signed on course.
     * @param courseId
     * @param profileType should be one of the following:
     * <ul><li>GenericDAO.PROFILE_TYPE_STUDENT</li>
     * <li>GenericDAO.PROFILE_TYPE_TUTOR</li></ul>
     * @return list of user identities.
     */
    protected List<UserIdentity> findUsersByNoCourse(int courseId, short profileType) {
        return session.createQuery("from UserIdentity as ui "
                + "where ui.account in "
                + "(select distinct p.account.id "
                + "from Profile p "
                + "where p.id.id = :profileType) "
                + "and  "
                + "ui.account not in "
                + "(select ud2.profile.account.id "
                + "from UserData ud2 "
                + "where ud2.profile.id.id = :profileType "
                + "and ud2.curriculumLevel.id = :courseId) "
                + "order by ui.name").setParameter("profileType", profileType).setParameter("courseId", courseId).list();
    }
    /* ================ / TOOLS SECTION ===================================== */
    /* ================ UNSORTED SECTION ==================================== */

    public List<CurriculumLevel> findCoursesByStudent(int studentId) {
        //return findCoursesByUser(studentId, PROFILE_TYPE_STUDENT);
        return findCoursesByStudent(studentId, null);
    }

    public List<CurriculumLevel> findCoursesByStudent(int studentId, String sort) {
        return findCoursesByUser(studentId, PROFILE_TYPE_STUDENT, sort);
    }

    public List<CurriculumLevel> findCoursesByStudentByTutor(int studentId, int tutorId) {
        return session.createQuery("select ud.curriculumLevel "
                + "from UserData ud "
                + "where ud.id.profileType = :tutorType "
                + "and ud.id.account = :tutorId "
                + "and ud.curriculumLevel.id in "
                + "(select ud2.curriculumLevel.id "
                + "from UserData ud2 "
                + "where ud2.id.profileType = :studentType "
                + "and ud2.id.account = :studentId)").setParameter("tutorType", PROFILE_TYPE_TUTOR).setParameter("tutorId", tutorId).setParameter("studentType", PROFILE_TYPE_STUDENT).setParameter("studentId", studentId).list();
    }

    public List<CurriculumLevel> findCoursesByNoStudentByTutor(int studentId, int tutorId) {
        return session.createQuery("select ud.curriculumLevel "
                + "from UserData ud "
                + "where ud.id.profileType = :tutorType "
                + "and ud.id.account = :tutorId "
                + "and ud.curriculumLevel.id not in "
                + "(select ud2.curriculumLevel.id "
                + "from UserData ud2 "
                + "where ud2.id.profileType = :studentType "
                + "and ud2.id.account = :studentId)").setParameter("tutorType", PROFILE_TYPE_TUTOR).setParameter("tutorId", tutorId).setParameter("studentType", PROFILE_TYPE_STUDENT).setParameter("studentId", studentId).list();
    }

    protected List<CurriculumLevel> findCoursesByUser(int userId, short profileType) {
        /*
        return session.createQuery("select ud.curriculumLevel "
        + "from UserData ud "
        + "where ud.id.account = :userId "
        + "and ud.id.profileType = :profileType "
        + "and ud.curriculumLevel.curriculumLevelType.id = :courseType " // + "order by ud.curriculumLevel.curriculumLevel.title, ud.ud.curriculumLevel.numeral"
        ).setParameter("userId", userId).setParameter("profileType", profileType).setParameter("courseType", CURRICULUM_LEVEL_TYPE_COURSE).list();
         */
        return findCoursesByUser(userId, profileType, null);
    }

    protected List<CurriculumLevel> findCoursesByUser(int userId, short profileType, String sort) {
        return session.createQuery("select ud.curriculumLevel "
                + "from UserData ud "
                + "where ud.id.account = :userId "
                + "and ud.id.profileType = :profileType "
                + "and ud.curriculumLevel.curriculumLevelType.id = :courseType " // + "order by ud.curriculumLevel.curriculumLevel.title, ud.ud.curriculumLevel.numeral"
                + "order by ud.curriculumLevel." + (sort == null ? "curriculumLevel.title" : sort)).setParameter("userId", userId).setParameter("profileType", profileType).setParameter("courseType", CURRICULUM_LEVEL_TYPE_COURSE).list();
    }

    public List<CurriculumLevel> findCoursesByNoStudent(int studentId) {
        return findCoursesByNoUser(studentId, PROFILE_TYPE_STUDENT);
    }

    public List<CurriculumLevel> findCoursesByNoTutor(int tutorId) {
        return findCoursesByNoUser(tutorId, PROFILE_TYPE_TUTOR);
    }

    protected List<CurriculumLevel> findCoursesByNoUser(int userId, short profileType) {
        /*
        return session.createQuery("select ud.curriculumLevel " +
        "from UserData ud " +
        "where ud.id.account <> :userId " +
        "and ud.id.profileType = :profileType " +
        "and ud.curriculumLevel.curriculumLevelType.id = :courseType "
        // + "order by ud.curriculumLevel.curriculumLevel.title, ud.ud.curriculumLevel.numeral"
        )
        .setParameter("userId", userId).setParameter("profileType", profileType).setParameter("courseType", CURRICULUM_LEVEL_TYPE_COURSE).list();
         */
        //new UserData().getId().
        return session.createQuery("from CurriculumLevel cl "
                + "where cl.curriculumLevelType.id = :courseType "
                + "and cl.id not in "
                + "(select ud2.id.curriculumLevel "
                + "from UserData ud2 "
                + "where ud2.id.account = :userId "
                + "and ud2.id.profileType = :profileType) "
                + "order by cl.curriculumLevel.title, cl.numeral").setParameter("userId", userId).setParameter("profileType", profileType).setParameter("courseType", CURRICULUM_LEVEL_TYPE_COURSE).list();
    }

    public CurriculumLevel addLab(int courseId, String labTitle, Integer labNumber, String description) {
        labNumber = labNumber == null ? 1 : Math.max(1, labNumber);

        //check is courseNumber free or taken
        if (session.createQuery("from CurriculumLevel "
                + "where curriculumLevelType.id = :labType "
                + "and curriculumLevel.id = :courseId "
                + "and numeral = :numeral").setParameter("labType", CURRICULUM_LEVEL_TYPE_LAB).setParameter("courseId", courseId).setParameter("numeral", labNumber).uniqueResult() != null) {
            labNumber = 1 + (Integer) session.createQuery("select max(numeral) from CurriculumLevel "
                    + "where curriculumLevelType.id = :labType "
                    + "and curriculumLevel.id = :courseId").setParameter("labType", CURRICULUM_LEVEL_TYPE_LAB).setParameter("courseId", courseId).uniqueResult();
        }


        CurriculumLevel course = new CurriculumLevel();
        course.setCurriculumLevelType(new CurriculumLevelType(CURRICULUM_LEVEL_TYPE_LAB));
        course.setCurriculumLevel((CurriculumLevel) session.get(CurriculumLevel.class, courseId));
        course.setTitle(labTitle);
        course.setNumeral(labNumber);
        course.setDescription(description);
        int labId = (Integer) session.save(course);

        // create WorkItems for new lab
        List<UserIdentity> studentList = findStudentsByCourse(courseId);
        for (UserIdentity student : studentList) {
            addWorkItem(student.getAccount(), labId);
        }

        CurriculumLevel savedLab = (CurriculumLevel) session.get(CurriculumLevel.class, labId);

        return savedLab;
    }

    public CurriculumLevel deleteLab(int labId) {
        CurriculumLevel lab = (CurriculumLevel) session.get(CurriculumLevel.class, labId);

        session.delete(lab);

        return lab;
    }

    public List<CurriculumLevel> findDirectionsByTutor(int tutorId) {
        //new CurriculumLevel().getCurriculumLevelType().getId();
        //new UserData().getId().getAccount();
        return session.createQuery("from CurriculumLevel cl "
                + "where cl.curriculumLevelType.id = :directionType "
                + "and cl.id in "
                + "(select ud2.curriculumLevel.curriculumLevel.id "
                + "from UserData ud2 "
                + "where ud2.id.account = :tutorId "
                + "and ud2.id.profileType = :tutorType)").setParameter("directionType", CURRICULUM_LEVEL_TYPE_DIRECTION).setParameter("tutorId", tutorId).setParameter("tutorType", PROFILE_TYPE_TUTOR).list();
    }

    public List<CurriculumLevel> findCoursesByTutorByDirection(int tutorId, int directionId) {
        return session.createQuery("from CurriculumLevel cl "
                + "where cl.curriculumLevelType.id = :courseType "
                + "and cl.curriculumLevel.id = :directionId "
                + "and cl.id in "
                + "(select ud2.id.curriculumLevel "
                + "from UserData ud2 "
                + "where ud2.id.account = :tutorId "
                + "and ud2.id.profileType = :tutorType)").setParameter("courseType", CURRICULUM_LEVEL_TYPE_COURSE).setParameter("directionId", directionId).setParameter("tutorId", tutorId).setParameter("tutorType", PROFILE_TYPE_TUTOR).list();
    }

    public CurriculumLevel findLab(int labId) {
        return (CurriculumLevel) session.get(CurriculumLevel.class, labId);
    }

    /* ================ / UNSORTED SECTION ================================== */
    /* ================= COMMENTED SECTION ================================== */
//    public int countDirections() {
//        return (int) count(CURRICULUM_LEVEL_TYPE_DIRECTION);
//    }
//
//    public int countCourses() {
//        return (int) count(CURRICULUM_LEVEL_TYPE_COURSE);
//    }
//
//    public long countLabs() {
//        return count(CURRICULUM_LEVEL_TYPE_LAB);
//    }
//    /**
//     * Counts curriculum levels of specified type
//     * @param curriculumLevelType type of curriculum levels to count
//     * @return count
//     */
//    protected long count(short curriculumLevelType) {
//        return (Long) session.createQuery("select distinct count(course) from CurriculumLevel course where "
//                + "course.curriculumLevelType.id = :curriculumLevelType").setParameter("curriculumLevelType", curriculumLevelType).uniqueResult();
//    }
//
//    public int countCoursesByTutor(int tutorId) {
//        return (int) countCurriculumLevelsByProfileType(tutorId, PROFILE_TYPE_TUTOR, CURRICULUM_LEVEL_TYPE_COURSE);
//    }
//
//    public int countCoursesByStudent(int studentId) {
//        return (int) countCurriculumLevelsByProfileType(studentId, PROFILE_TYPE_STUDENT, CURRICULUM_LEVEL_TYPE_COURSE);
//    }
//
//    protected long countCurriculumLevelsByProfileType(int userId, short profileType, short curriculumLevelType) {
//        return (Long) session.createQuery("select distinct count(userData) "
//                + "from UserData userData "
//                + "where userData.id.account = :userId "
//                + "and userData.profile.profileType.id = :profileType "
//                + "and userData.curriculumLevel.curriculumLevelType.id = :curriculumLevelType").setParameter("userId", userId).setParameter("profileType", profileType).setParameter("curriculumLevelType", curriculumLevelType).uniqueResult();
//    }
//    /**
//     * Finds all curriculum levels with type = GenericDAO.CURRICULUM_LEVEL_TYPE_COURSE
//     * @return list of curriculum levels
//     */
//    public List<CurriculumLevel> findAllCourses() {
//        return createAllCoursesCriteria(null).list();
//    }
    /* =============== / COMMENTED SECTION ================================== */
}
